Imports System.IO
Imports System.Text
Imports System.Collections.Specialized
Imports System.Collections.Generic
Imports Cdm.AppServices.Log
Imports Cdm.DataCore
Imports Cdm.DataCore.Entities
Imports Cdm.DataCore.Budget
Imports Cdm.DataCore.LookupTables
Imports Cdm.DataCore.UserSecurity
Imports System.Data
Imports GemBox.Spreadsheet
Imports FMSADataConnectorController

Partial Class ExpenditureReportBatchUpload
    Inherits System.Web.UI.Page

#Region "CONSTANTS"
    Private Const _CLASS_NAME = "ExpenditureReportBatchUpload"
    Private Const _DEFAULT_DROPDOWNLIST_VALUE = -1
    Private Const _DEFAULT_DROPDOWNLIST_TEXT = ""
#End Region
    Dim categoryHashTable As Hashtable
    Dim m_SelectedParticipantList As Hashtable = New Hashtable()


    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


        If FileUpload1.HasFile Then
            Try
                Dim ExpenditureFileDropLocation As String = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings.Get("SpendingPlanDrop"))

                FileUpload1.SaveAs(ExpenditureFileDropLocation & _
                   FileUpload1.FileName)

                Try

                    'Load CSV file into DataTable
                    Dim dc As FMSADataConnectorController.DataController = New FMSADataConnectorController.DataController()
                    Dim strServerFileLocation As String = ExpenditureFileDropLocation + FileUpload1.FileName
                    If strServerFileLocation.ToLower().EndsWith(".xls") Then
                        strServerFileLocation = Spreadsheet.BulkUpload.SaveAsCSV(strServerFileLocation)
                    End If
                    Dim expenditureDataTable As Data.DataTable = dc.LoadExpenditureFile(strServerFileLocation, UserAuthorization.CurrentUser.LogonId)

                    'Bulk import DataTable into database
                    Dim strConnectionString As String = EntityController.AppConnectString
                    dc.ImportExpenditureData(strConnectionString, expenditureDataTable)


                    'Send notifications of successful import
                    dc.SendBatchNotificationMessage(EntityController.AppConnectString, 28005, FileUpload1.FileName + " was uploaded to staging on " + Now() + ".")

                    'Get spending plan items for this active spending plan
                    Dim dr As System.Data.DataRow
                    Dim t_Id As String = "0"
                    Dim t_CurrentId = "-1"
                    Dim t_BatchUploadActivitySqlClause As String = "INSERT INTO ExpenditureBatchUploadActivity (SpendingPlanId, UserId, DateUploaded, FileName) "
                    Dim t_BatchUploadActivitySqlValuesClause As String = ""
                    Dim t_BatchUploadActivitySqlCommand As Data.SqlClient.SqlCommand

                    For Each dr In expenditureDataTable.Rows
                        t_Id = dr.Item("SpendingPlanId").ToString()
                        If t_Id <> t_CurrentId Then
                            t_BatchUploadActivitySqlValuesClause = " VALUES(" + t_Id.ToString() + ", " + UserAuthorization.CurrentUser.UserId.ToString() + ", '" + System.DateTime.Now() + "', '" + FileUpload1.FileName.ToString() + "')"
                            t_BatchUploadActivitySqlCommand = New Data.SqlClient.SqlCommand(t_BatchUploadActivitySqlClause + t_BatchUploadActivitySqlValuesClause, New Data.SqlClient.SqlConnection(strConnectionString))
                            t_BatchUploadActivitySqlCommand.Connection.Open()
                            t_BatchUploadActivitySqlCommand.ExecuteNonQuery()
                            t_CurrentId = t_Id
                        End If
                    Next

                    Try
                        'Delete temporary file from web server directory
                        System.IO.File.Delete(FileUpload1.FileName)

                    Catch ex As Exception
                        ResultLabel.Visible = True
                        ResultLabel.Text = "ERROR: The import was successful but the temporary import file failed to delete from the web server directory."

                    End Try

                    'Update UI with notification sent and process is complete
                    If expenditureDataTable.Rows.Count = 0 Then
                        ResultLabel.Visible = True
                        ResultLabel.Text = "There are no payment items in the file.  Make sure that each payment row in your file has a Date of Payment specified."
                    Else
                        Response.Redirect("ExpenditureReportBatchConfirm.aspx")
                    End If

                Catch ex As Exception
                    Dim dc As FMSADataConnectorController.DataController = New FMSADataConnectorController.DataController()
                    dc.SendBatchNotificationMessage(EntityController.AppConnectString, 28006, "Error uploading file " + FileUpload1.FileName + "." + ex.Message.ToString())
                    ResultLabel.Visible = True
                    ResultLabel.Text = "ERROR: " & ex.Message.ToString()

                End Try

            Catch ex As Exception
                Dim dc As FMSADataConnectorController.DataController = New FMSADataConnectorController.DataController()
                dc.SendBatchNotificationMessage(EntityController.AppConnectString, 28006, "Error uploading file " + FileUpload1.FileName + "." + ex.Message.ToString())
                ResultLabel.Visible = True
                ResultLabel.Text = "ERROR: " & ex.Message.ToString()

            End Try
        Else
            ResultLabel.Visible = True
            ResultLabel.Text = "You have not specified a file."
        End If
    End Sub

    'Private Sub TrialDownLoad()

    'Dim excelEngine As Syncfusion.XlsIO.ExcelEngine = New Syncfusion.XlsIO.ExcelEngine()
    ' Dim book1 As Syncfusion.XlsIO.IWorkbook = excelEngine.Excel.Workbooks.Add()
    ' Dim sheet1 As Syncfusion.XlsIO.IWorksheet = book1.Worksheets(0)


    '     For iColumn As Integer = 1 To 27
    '         For iRow As Integer = 1 To 12064
    '             sheet1.Range(iRow, iColumn).Text = "Row " + CStr(iRow) + ", Col " + CStr(iColumn)
    '         Next
    '     Next

    ' Dim saveFile As String = "C:\Source Code\NPO2008\CDMWeb\files\SpendingPlanTrial.xls"
    '     book1.SaveAs(saveFile)

    '    Response.ClearContent()
    '    Response.ClearHeaders()
    '    Response.Clear()
    '    Response.ContentType = "text/csv" '"application/vnd.ms-excel"
    '    Response.AddHeader("Content-Disposition", "attachment;filename=ActiveSPBatch.xls") '& t_ExpenditureFileName)
    '    Response.AddHeader("Target", "newwindow")
    '    Response.WriteFile(saveFile)


    'End Sub


    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            Me.EndDateInputControl.Value = DateTime.Now.ToShortDateString()
            Me.StartDateInputControl.Value = DateTime.Now.AddMonths(-1)
        End If
        errorDownloadLabel.Visible = False
    End Sub

    Protected Sub DownloadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DownloadButton.Click

        Dim errorMessage As String = ""
        If Not IsDate(StartDateInputControl.Value) Then
            errorMessage = "Please enter a valid Start Date."
        End If

        If Not IsDate(EndDateInputControl.Value) Then
            errorMessage = String.Concat(errorMessage, "  Please enter a valid End Date.")
        End If

        If errorMessage.Length > 0 Then
            ShowError(errorMessage)
            Exit Sub
        End If

        Dim StartDate As DateTime = Convert.ToDateTime(StartDateInputControl.Value)
        Dim EndDate As DateTime = Convert.ToDateTime(EndDateInputControl.Value)

        If (EndDate < StartDate) Then
            ShowError("End date is earlier than start date.")
            Exit Sub
        End If

        Dim ApprovedSpendingPlanList As List(Of ApprovedSpendingPlan) = ApprovedSpendingPlan.ListByDateRange(StartDate, EndDate, UserAuthorization.CurrentUser.UserId)
        Dim ApprovedPlan As ApprovedSpendingPlan
        Dim t_SpendingPlanId As Integer = 0
        Dim t_ExpenditureReportId As Integer = 0
        Dim ExpenditureFileDropLocation As String = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings.Get("SpendingPlanDrop"))
        'ExpenditureFileDropLocation = "c:\uploads\" 'REMOVE
        Dim t_ExpenditureFileName As String = ExpenditureFileDropLocation + CType(Session("CurrentUserId"), String) + ".xls"
        Dim t_templateName As String = ExpenditureFileDropLocation + "blankpage.xls"

        'Dim t_File = New FileInfo(t_ExpenditureFileName)
        'Dim t_FileStream As StreamWriter = t_File.CreateText()
        If System.IO.File.Exists(t_ExpenditureFileName) Then
            System.IO.File.Delete(t_ExpenditureFileName)
        End If

        Dim t_FileRowId As Integer = 1
        Dim t_BudgetItemNumber As Integer = 0
        Dim t_FileCategoryHeaderList As String = ""
        Dim t_BudgetCategoryIdString As String = ""
        Dim t_FieldPosition As Integer = 0

        Dim t_SpendingPlanItemSQL As String = ""
        Dim t_SpendingPlanItemSqlCommand As Data.SqlClient.SqlCommand
        Dim t_SpendingPlanItemSqlDataReader As Data.SqlClient.SqlDataReader
        Dim t_ConnectionString As String = EntityController.AppConnectString '"Data Source=10.1.2.12;Initial Catalog=CDM_GN;User ID=sa;Password=sycam0re"
        'Dim t_SpendingPlanItemSqlString As String = "SELECT SpendingPlanItemId, SpendingPlanId, BudgetCategoryHeaderId, BudgetItemNumber, ItemValue, LastModified, DateModified FROM SpendingPlanItem"
        Dim t_SpendingPlanItemSqlString As String = "spSpendingPlanItemList "


        'Map spending plan fields to field position in file
        'We need to put this mapping into the LuAdminBudgetCategoryHeader table or mapping file
        Dim batchColumnList As New FMSADataConnectorController.BatchUploadColumnList()
        Dim batchSpreadSheet As New Spreadsheet.BulkUpload(t_ExpenditureFileName, t_templateName)
        batchSpreadSheet.WriteHeaders(batchColumnList)

        Dim bShowParticipant As Boolean = GetShowParticipant()
        'Loop through the list of approved spending plans
        For Each ApprovedPlan In ApprovedSpendingPlanList
            Dim participantName As String = ""

            t_FileCategoryHeaderList = ""
            If bShowParticipant Then
                Dim currentParticipant As Participant = Participant.Retrieve(ApprovedPlan.ParticipantId)
                participantName = currentParticipant.FullName
            End If
            t_SpendingPlanId = ApprovedPlan.SpendingPlanId
            If t_SpendingPlanId > 0 Then

                'Get spending plan items for this active spending plan
                t_SpendingPlanItemSqlCommand = New Data.SqlClient.SqlCommand(t_SpendingPlanItemSqlString + t_SpendingPlanId.ToString(), New Data.SqlClient.SqlConnection(t_ConnectionString))
                t_SpendingPlanItemSqlCommand.Connection.Open()
                t_SpendingPlanItemSqlDataReader = t_SpendingPlanItemSqlCommand.ExecuteReader(Data.CommandBehavior.CloseConnection)

                With t_SpendingPlanItemSqlDataReader

                    t_BudgetItemNumber = "-1"
                    batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.RowId, t_FileRowId)
                    batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.SpendingPlanId, ApprovedPlan.SpendingPlanId.ToString())
                    batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ParticipantId, ApprovedPlan.ParticipantId.ToString())
                    batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.MedicaidNumber, ApprovedPlan.MedicaidNumber)
                    batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ParticipantName, participantName)

                    batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ItemStartDate, ApprovedPlan.SpendingPlanStartDate.ToShortDateString())
                    batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ItemEndDate, ApprovedPlan.SpendingPlanEndDate.ToShortDateString())

                    'Do while more line items
                    Do While .Read = True


                        'If new spending plan line item, then write current line item to file and start new line item in memory
                        If (t_BudgetItemNumber <> -1) And (.GetInt32(3) <> t_BudgetItemNumber) Then
                            'batchSpreadSheet.NextRow()
                            t_FileCategoryHeaderList = t_FileCategoryHeaderList + ";"
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.CategoryHeaderList, t_FileCategoryHeaderList)
                            'Write line item to file
                            batchSpreadSheet.NextRow()
                            t_FileRowId = t_FileRowId + 1
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.RowId, t_FileRowId)
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.SpendingPlanId, ApprovedPlan.SpendingPlanId.ToString())
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ParticipantId, ApprovedPlan.ParticipantId.ToString())
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.MedicaidNumber, ApprovedPlan.MedicaidNumber)
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ParticipantName, participantName)


                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ItemStartDate, ApprovedPlan.SpendingPlanStartDate.ToShortDateString())
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ItemEndDate, ApprovedPlan.SpendingPlanEndDate.ToShortDateString())



                            t_FileCategoryHeaderList = ""  'Category header list
                            t_BudgetItemNumber = "-1"  'Budget line item

                        End If

                        t_BudgetCategoryIdString = .GetInt32(2)
                        t_FieldPosition = batchColumnList.GetMappedColumnNumber(t_BudgetCategoryIdString)
                        If t_FieldPosition <> 0 Then 'Do not include field in download file
                            Dim bReadOnly As Boolean = .GetBoolean(8)
                            'Update category header id's

                            If bReadOnly = False Then
                                If t_FileCategoryHeaderList.Length > 0 Then
                                    t_FileCategoryHeaderList = t_FileCategoryHeaderList + ";"
                                End If
                                t_FileCategoryHeaderList = t_FileCategoryHeaderList + t_BudgetCategoryIdString
                            End If
                            'Budget line item
                            t_BudgetItemNumber = .GetInt32(3)
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ItemId, .GetInt32(3))

                            't_FieldPosition = t_FieldPosition - 1
                            't_FieldArrayList(t_FieldPosition) = .GetString(4)
                            batchSpreadSheet.SetCurrentRowValue(BatchUploadColumnList.ColumnNumber.ExpenditureType, .GetString(7))

                            batchSpreadSheet.SetHighlightedValue(t_FieldPosition, .GetString(4), bReadOnly)
                        End If
                    Loop

                End With


            End If

        Next    'Next Approved Spending Plan

        batchSpreadSheet.SaveAndClose(bShowParticipant)
        Dim f As New FileInfo(t_ExpenditureFileName)
        Dim fileByteLength As String = f.Length.ToString()   't_FileStream.BaseStream.Length.ToString()
        't_FileStream.Close()

        ResultLabel.Visible = False
        Response.ClearContent()
        Response.ClearHeaders()
        Response.Clear()
        Response.ContentType = "text/csv" '"application/vnd.ms-excel"
        Response.AppendHeader("content-length", fileByteLength)
        Response.AddHeader("Content-Disposition", "attachment;filename=ActiveSPBatch.xls") '& t_ExpenditureFileName)
        Response.AddHeader("Target", "newwindow")
        Response.WriteFile(t_ExpenditureFileName)

    End Sub

    Private Function GetShowParticipant() As Boolean
        Try
            Return CBool(Application("ShowParticipantOnBulkUpload"))
        Catch
            Return True
        End Try
    End Function

    Private Sub ShowError(ByVal message As String)
        Me.errorDownloadLabel.Text = message
        Me.errorDownloadLabel.Visible = True
    End Sub

    Private Function GetColumnHeaderList(ByVal batchColumns As FMSADataConnectorController.BatchUploadColumnList) As String
        Dim headerResults As New Hashtable
        Dim headerList As List(Of Cdm.DataCore.LookupTables.LuAdminBudgetCategoryHeader) = Cdm.DataCore.LookupTables.LuAdminBudgetCategoryHeader.ListAll()
        Dim categoryList As Hashtable = GetCategoryHashTable()
        Dim columnNumber As Integer
        Dim sb As New StringBuilder()

        'Initialize all items to string
        For i As Integer = 1 To batchColumns.ColumnCount
            headerResults.Add(i, "")
        Next

        'Get the current list of headers from the database and set their display names
        For i As Integer = 0 To headerList.Count - 1
            columnNumber = batchColumns.GetMappedColumnNumber(headerList(i).BudgetCategoryHeaderId.ToString())
            If columnNumber > 0 Then
                Dim cat As Cdm.DataCore.LookupTables.LuAdminBudgetCategory = CType(categoryList(headerList(i).BudgetCategoryId), Cdm.DataCore.LookupTables.LuAdminBudgetCategory)
                'Show the CategoryHeader display name;
                'If it is a zero lines Category, then display the category display name.
                If cat.NumberOfLines > 0 Then
                    headerResults(columnNumber) = ReplaceHeaderResults(headerResults(columnNumber), headerList(i).DisplayName)
                Else
                    headerResults(columnNumber) = ReplaceHeaderResults(headerResults(columnNumber), cat.DisplayName)
                End If
            End If
        Next

        For i As Integer = 1 To batchColumns.ColumnCount
            If headerResults(i).Equals("") Then
                headerResults(i) = batchColumns.GetColumnDisplayName(i)
            End If
            sb.Append(headerResults(i))
            If i < batchColumns.ColumnCount Then
                sb.Append(",")
            End If
        Next

        Return sb.ToString()

    End Function

    Private Function GetCategoryHashTable() As Hashtable
        If categoryHashTable Is Nothing Then
            categoryHashTable = New Hashtable()
            Dim categories As List(Of Cdm.DataCore.LookupTables.LuAdminBudgetCategory) = Cdm.DataCore.LookupTables.LuAdminBudgetCategory.ListAll()
            For i As Integer = 0 To categories.Count - 1
                categoryHashTable.Add(categories(i).BudgetCategoryId, categories(i))
            Next
        End If
        Return categoryHashTable
    End Function

    Private Function GetCategoryDisplayName(ByVal categoryId As Integer) As String
        Dim cat As LuAdminBudgetCategory = GetCategoryHashTable()(categoryId)
        Return cat.DisplayName
    End Function

    Private Function ReplaceHeaderResults(ByVal oldName As String, ByVal newDisplay As String) As String
        If String.IsNullOrEmpty(oldName) Then
            Return newDisplay
        Else
            If oldName.ToUpper.Contains(newDisplay.ToUpper()) Then
                Return oldName
            Else
                Return String.Concat(oldName, "; ", newDisplay)
            End If
        End If
    End Function
End Class
